package com.lvxh.exp.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;

import com.lvxh.exp.bean.TableProperties;

public class DBUtil
{

    public static boolean SYNC_NDFZ_STATUS = false;

    public Connection getConnection(String driver, String url, String userName,
            String pwd)
    {
        System.out.println("connection to database the params is: Driver["
                + driver + "] url [" + url + " ] userName [" + userName
                + " ] pwd [" + pwd + "]");
        Connection con = null;
        try
        {
            Class.forName(driver);
            con = DriverManager.getConnection(url, userName, pwd);
        }
        catch (ClassNotFoundException e)
        {
            System.out.println(e);
        }
        catch (SQLException e)
        {
            System.out.println(e);
        }
        System.out.println("connection to database result is: " + con);
        return con;
    }

    public ResultSet query(String sql, Connection con, Statement ps)
    {
        ResultSet result = null;
        try
        {
            result = ps.executeQuery(sql);
        }
        catch (SQLException e)
        {
            System.out.println(e);
        }

        return result;
    }

    public ResultSet query(String sql, Connection con, List<Object> list)
    {
        ResultSet result = null;
        PreparedStatement ps = null;
        try
        {
            ps = con.prepareStatement(sql);
            for (int i = 0; i < list.size(); i++)
            {
                ps.setObject(i + 1, list.get(i));

            }
            result = ps.executeQuery();
        }
        catch (SQLException e)
        {
            System.out.println(e);
        }

        return result;
    }

    public void close(Object obj)
    {
        if (obj == null)
        {
            return;
        }
        if (obj instanceof Connection)
        {
            try
            {
                ((Connection) obj).close();
            }
            catch (SQLException e)
            {
                System.out.println(e);
            }
        }
        else if (obj instanceof Statement)
        {
            try
            {
                ((Statement) obj).close();
            }
            catch (SQLException e)
            {
                System.out.println(e);
            }
        }
        else if (obj instanceof ResultSet)
        {
            try
            {
                ((ResultSet) obj).close();
            }
            catch (SQLException e)
            {
                System.out.println(e);
            }
        }
    }

    public static List<String> getTablesName(String driver, String url,
            String username, String pwd)
    {
        DBUtil util = new DBUtil();
        Connection con = util.getConnection(driver, url, username, pwd);
        String sql = "show tables";
        PreparedStatement ps = null;
        ResultSet result = null;
        List<String> list = new ArrayList<String>();
        try
        {
            ps = con.prepareStatement(sql);
            result = util.query(sql, con, ps);
            while (result.next())
            {
                list.add(result.getString(1).toLowerCase(Locale.getDefault()));
            }
        }
        catch (SQLException e)
        {

            e.printStackTrace();
        }
        finally
        {
            util.close(result);
            util.close(ps);
            util.close(con);
        }
        return list;
    }

    public static List<TableProperties> getTablesProperty(String tableName,
            String driver, String url, String username, String pwd)
    {
        DBUtil util = new DBUtil();
        // Connection con = util.getConnection("com.mysql.jdbc.Driver",
        // "jdbc:mysql://192.168.2.103:3306/expweb", "root", "root");
        Connection con = util.getConnection(driver, url, username, pwd);
        String sql = "describe " + tableName;
        PreparedStatement ps = null;
        ResultSet result = null;
        List<TableProperties> list = new ArrayList<TableProperties>();
        try
        {
            ps = con.prepareStatement(sql);
            result = util.query(sql, con, ps);
            while (result.next())
            {
                TableProperties bean = new TableProperties();
                bean.setField(result.getString(1));
                bean.setType(result.getString(2));
                bean.setIsNull(result.getString(3));
                bean.setKey(result.getString(4));
                bean.setDefaultValue(result.getString(5));
                bean.setExtra(result.getString(6));
                list.add(bean);
            }
        }
        catch (SQLException e)
        {

            e.printStackTrace();
        }
        finally
        {
            util.close(result);
            util.close(ps);
            util.close(con);
        }
        return list;
    }

}
